package com.sqlite 
{
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.MouseEvent;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;

	/**
	 * 处理sqlite数据库工具
	 * SQLite 数据库操作: https://www.runoob.com/sqlite/sqlite-insert.html
	 * @author clong 2019/6/23 22:56
	 */
	public class SQLiteUtils 
	{
		
		//private var db:File = File.applicationStorageDirectory.resolvePath("data/users.db"); 
		//private var conn:SQLConnection; 
		//private var createTableStmt:SQLStatement; 
		private var createTableSQL:String = 
			"CREATE TABLE IF NOT EXISTS User (" + 
			"userId INTEGER PRIMARY KEY AUTOINCREMENT," + 
			"firstName TEXT," + "lastName TEXT)"; 
		private var selectStmt:SQLStatement; 
		private var selectSQL:String = "SELECT * FROM User"; 
		private var insertStmt:SQLStatement; 
		private var insertSQL:String = 
			"INSERT INTO User (firstName, lastName)" + 
			"VALUES (:firstName, :lastName)"; 
			
		/**
		 * 创建表	
		 * @param	tableName
		 * @return 	create table if not exists {table} ({fields})
		 */
		public static function createTable( tableName:String , fields:Vector.<SQLField> = null ):String{
			
			var fieldstr:String = "";			
			if ( fields ){
				var arr:Array = [];
				for (var i:int = 0; i < fields.length;i++ ){
					var value:String = fields[i] ? fields[i].name : "";
					if (value){
						arr.push( fields[i].toString() );
					}
				}
				fieldstr = arr.join( " , " );
				fieldstr = " ( " + fieldstr + ")";
			}
			
			return "create table if not exists " + tableName + fieldstr;
		}
		
		/**
		 * 查询表	"select {*} from {table} where {where};
		 * @param	tableName 	表名
		 * @param 	params		参数数据
		 * @param	where		条件参数
		 * @return
		 */
		public static function queryTable( table:String , params:Object = null , where:Object = null ):String{
			
			var keys:Array = [];
			var values:Array = [];
			var key:String;
			if ( params != null ){
				for (key in params){
					keys.push( key );
				}
			}			
			var p1:String = keys.length > 0 ? keys.join( "," ) : "*";
			
			values = [];
			var p2:String = "";
			if (where){
					for (key in where){
					keys.push( key );
					values.push( key + " = '" + where[key] + "'" );
				}
				p2 = "where " + values.join( "," );
			}
			
			return "select "+p1+" from " + table + p2;
		}
		
		/**
		 * 添加字段
		 * @param	filed		字段名
		 * @param	tableName	表名
		 * @param 	tableType 	字段类型 SQLField.INTEGER
		 * @return
		 */
		public static function addField( filed:String , tableName:String , tableType:String ):String{
			
			return "ALTER TABLE "+tableName+" ADD "+filed+" sex " + tableType;
		}
		
		/**
		 * 移除字段 alter table {0} drop column {1};
		 * @param	filed	字段名
		 * @param	table	表名
		 * @return
		 */
		public static function removeField( filed:String , table:String ):String{
			
			return "alter table "+table+" drop column "+filed;
		}
		
		/**
		 * 删除表 drop table {name}
		 * @param	name	表名
		 */
		public static function deleteTable( name:String ):String{
			
			return "drop table " + name;
		}
		
		/**
		 * 删除表中数据 delete from {table} where {where}
		 * @param	table
		 * @param	where
		 */
		public static function deleteTableData( table:String , where:Object ):String{
			
			var values:Array = [];
			var p2:String = "";
			var key:String;
			if (where){
				for (key in where){
					values.push( key + " = " + where[key] );
				}
				p2 = " where " + values.join( "," );
			}
			
			return "delete from " + table + p2;
		}
		
		/**
		 * 插入表
		 * INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)] (value1, value2, value3,...valueN);
		 * INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
		 * @param	tableName
		 * @param 	params 参数 如:{"column1":column1,"column2":column2};
		 */
		public static function insertInto( tableName:String , params:Object ):String{
			
			if ( !tableName || !params ){
				return "";
			}
			var keys:Array = [];
			var values:Array = [];
			var key:String;
			for (key in params){
				keys.push( key );
				values.push( params[key] );
			}
			return "insert into "+tableName+" (" + keys.join(",") + ") values (" + values.join(",") + ");";
		}
		
		/**
		 * 更新表
		 * @param	table
		 * @param	params
		 * @param	where
		 * @return
		 */
		public static function updateTable( table:String , params:Object , where:Object = null ):String{
			
			var keys:Array = [];
			var values:Array = [];
			var key:String;
			for (key in params){
				keys.push( key );
				values.push( key + " = '" + params[key] + "'" );
			}
			var p1:String = values.join( "," );
			
			values = [];
			var p2:String = "";
			if (where){
					for (key in where){
					keys.push( key );
					values.push( key + " = '" + where[key] + "'" );
				}
				p2 = "where " + values.join( "," );
			}			
			
			return replaceHolder( "update {0} set {1} {2};" , [ table , p1 , p2 ] );
		}
		
		/**
		 * 替换占位符 2012.11.15
		 * @param	str 字符串中含占位符，如: xxx{0}xxx{1}
		 * @param	arr
		 * @return
		 */
		private static function replaceHolder(str:String, arr:Array = null):String {

			if (arr) {

				var ind:int;
				var temp:String;

				str = str.replace(/\{[0-9]\}/g, function():String {

					temp = arguments[0].replace(/\{/g, "")
					ind = int(temp.replace(/\}/g, ""));

					return arr[ind];
				});
			}
			return str;
		}
	}
}